package com.example.mapper;

import com.example.pojo.HeritageProject;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface HeritageProjectMapper {
    //查询所有
    @Select("SELECT i.id,i.name, i.species_id, i.region_id, i.description, i.url,i.content,  p.region_name regionName, s.species_name speciesName " +
            "FROM heritage_project i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id") // 使用 JOIN 查询
    List<HeritageProject> findAll();

    //根据区域和物种分类查询
    @Select("SELECT i.id, i.name, i.species_id, i.region_id, i.description, i.url, i.content, p.region_name regionName, s.species_name speciesName " +
            "FROM heritage_project i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id " +
            "WHERE (#{regionId} IS NULL OR i.region_id = #{regionId}) " +
            "AND (#{speciesId} IS NULL OR i.species_id = #{speciesId})")
    List<HeritageProject> findAllByCategory(@Param("regionId") Integer regionId, @Param("speciesId") Integer speciesId);
    // 根据地区名称查询地区ID
    @Select("SELECT id FROM heritage_category_region WHERE region_name = #{regionName}")
    Integer findRegionIdByName(@Param("regionName") String regionName);
    // 根据种类名称查询种类ID
    @Select("SELECT id FROM heritage_category_species WHERE species_name = #{speciesName}")
    Integer findSpeciesIdByName(@Param("speciesName") String speciesName);
    // 插入新项目
    @Insert("INSERT INTO heritage_project(name, species_id, region_id, description, url, content) " +
            "VALUES(#{name}, #{speciesId}, #{regionId}, #{description}, #{url}, #{content})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(HeritageProject project);

    // 更新项目信息
    @Update("UPDATE heritage_project SET name=#{name}, species_id=#{speciesId}, region_id=#{regionId}, description=#{description}, " +
            "url=#{url}, content=#{content} WHERE id=#{id}")
    void update(HeritageProject project);

    // 删除项目
    @Delete("DELETE FROM heritage_project WHERE id=#{id}")
    void delete(@Param("id") Integer id);

    // 根据ID查询项目
    @Select("SELECT i.id,i.name, i.species_id, i.region_id, i.description, i.url,i.content,  p.region_name regionName, s.species_name speciesName " +
            "FROM heritage_project i " +
            "LEFT JOIN heritage_category_region p ON i.region_id = p.id " +
            "LEFT JOIN heritage_category_species s ON i.species_id = s.id " +
            "WHERE i.id = #{id}")
    HeritageProject findById(@Param("id") Integer id);
}
